﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using QuanLyDuAn.DTO;
using QuanLyDuAn.DataAccess;
using System.Data.SqlClient;
using System.Data;
using System.Collections;

namespace QuanLyDuAn.BUS
{
    class ApplicantController
    {
        DataSet ds,dsName;
        SqlCommand cmd;
        SqlDataAdapter da;
        //doc du lieu tu datasouce va doc du lieu tu dataset
        SqlConnection conn;
        SqlDataReader reader;
        dbAccess database = new dbAccess();
        public DataSet LoadApplicant()
        {
            conn = database.GetConnect();
            conn.Open();
            ds = new DataSet();
            cmd = new SqlCommand("getAllApplicant", conn);
            da = new SqlDataAdapter(cmd);
            da.Fill(ds, "Applicant");
            return ds;
        }
        public DataSet LoadApplicant_period(String period)
        {
            conn = database.GetConnect();
            conn.Open();
            ds = new DataSet();
            cmd = new SqlCommand("getAllApplicant_period", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@period", SqlDbType.VarChar).Value = period;
            da = new SqlDataAdapter(cmd);
            da.Fill(ds, "Applicant");
            return ds;
        }


        public void InsertApplicant(String id, String date, String status, String name, String datebirth, String phone, String qualification, int experience)
        {
            conn = database.GetConnect();
            conn.Open();
            cmd = new SqlCommand("insertApplicant", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@aId", SqlDbType.VarChar).Value = id;
            cmd.Parameters.Add("@aDate", SqlDbType.DateTime).Value = date;
            cmd.Parameters.Add("@aStatus", SqlDbType.VarChar).Value = status;
            cmd.Parameters.Add("@aName ", SqlDbType.VarChar).Value = name;
            cmd.Parameters.Add("@aDateBirth", SqlDbType.DateTime).Value = datebirth;
            cmd.Parameters.Add("@aPhone", SqlDbType.VarChar).Value = phone;
            cmd.Parameters.Add("@aQualification", SqlDbType.VarChar).Value = qualification;
            cmd.Parameters.Add("@aExperience", SqlDbType.Int).Value = experience;
            int i = cmd.ExecuteNonQuery();
        }
        public void UpdateApplicant(String id, String date, String status, String name, String datebirth, String phone, String qualification, int experience)
        {
            conn = database.GetConnect();
            conn.Open();
            cmd = new SqlCommand("updateApplicant", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@aId", SqlDbType.VarChar).Value = id;
            cmd.Parameters.Add("@aDate", SqlDbType.DateTime).Value = date;
            cmd.Parameters.Add("@aStatus", SqlDbType.VarChar).Value = status;
            cmd.Parameters.Add("@aName ", SqlDbType.VarChar).Value = name;
            cmd.Parameters.Add("@aDateBirth", SqlDbType.DateTime).Value = datebirth;
            cmd.Parameters.Add("@aPhone", SqlDbType.VarChar).Value = phone;
            cmd.Parameters.Add("@aQualification", SqlDbType.VarChar).Value = qualification;
            cmd.Parameters.Add("@aExperience", SqlDbType.Int).Value = experience;
            int i = cmd.ExecuteNonQuery();
        }
        public void DelApplicant(String id)
        {
            conn = database.GetConnect();
            conn.Open();
            cmd = new SqlCommand("DelApplicant", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@aId", SqlDbType.VarChar).Value = id;
            int i = cmd.ExecuteNonQuery();
        }
        public DataSet SearchApplicantByName(String name)
        {
            conn = database.GetConnect();
            conn.Open();
            dsName = new DataSet();
            cmd = new SqlCommand("searchApplicantName", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@aName", conn).Value = name;
            da = new SqlDataAdapter(cmd);
            da.Fill(dsName, "Applicant");
            return dsName;
        }
        public DataSet SearchApplicantByDate(String date)
        {
            conn = database.GetConnect();
            conn.Open();
            ds = new DataSet();
            cmd = new SqlCommand("searchApplicantByDate", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@aDate", SqlDbType.DateTime).Value = date;
            da = new SqlDataAdapter(cmd);
            da.Fill(ds, "Applicant");
            return ds;
        }
        public ArrayList GetApplicantName()
        {
            ArrayList list = new ArrayList();
            conn = database.GetConnect();
            conn.Open();
            cmd = new SqlCommand("getNameApplicant", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                String name = reader.GetString(0);
                list.Add(name);
            }
            return list;
        }
        public DataSet SearchApplicantByStatus(String status)
        {
            conn = database.GetConnect();
            conn.Open();
            ds = new DataSet();
            cmd = new SqlCommand("searchApplicantByStatus", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@aStatus", SqlDbType.VarChar).Value = status;
            da = new SqlDataAdapter(cmd);
            da.Fill(ds, "Applicant");
            return ds;
        }
        public DataSet StatisticsApplicant(String datetime1, String datetime2)
        {
            conn = database.GetConnect();
            conn.Open();
            ds = new DataSet();
            cmd = new SqlCommand("statisticsApplicant", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@aDate1", SqlDbType.DateTime).Value = datetime1;
            cmd.Parameters.Add("@aDate2", SqlDbType.DateTime).Value = datetime2;
            da = new SqlDataAdapter(cmd);
            da.Fill(ds, "Applicant");
            return ds;
        }
        public DataSet CheckApplicant(String aId)
        {
            conn = database.GetConnect();
            conn.Open();
            ds = new DataSet();
            cmd = new SqlCommand("CheckApplicant", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@aId", SqlDbType.VarChar).Value = aId;
            da = new SqlDataAdapter(cmd);
            da.Fill(ds, "Applicant");
            return ds;
        }

        public DataSet LoadApplicantOpen()
        {
            conn = database.GetConnect();
            conn.Open();
            ds = new DataSet();
            cmd = new SqlCommand("LoadApplicantOpen", conn);
            da = new SqlDataAdapter(cmd);
            da.Fill(ds, "Applicant");
            return ds;
        }

        public String GetApplicantStatus(String aID)
        {
            conn = database.GetConnect();
            conn.Open();
            cmd = new SqlCommand("CheckStatusApplicant", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@aID", SqlDbType.VarChar).Value = aID;
            reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                String status = reader.GetString(0);
                return status;
            }
            return null;
        }
    }
}
